﻿Imports jxl
Imports jxl.write
Imports System.IO
Imports System.Data
Imports System.Collections
Imports System.Data.SqlClient

Public Class clsEscribirExcel
#Region "DECLARACION DE PROPIEDADES"
    Private _RutaArchivo As String
    Private _RegistrosXHoja As Integer
    Private _ErrorMessage As String
    Private _NombreHoja As String
    Private _FilaInicial As Integer
    Private _ColumnaInicial As Integer
    Private _TituloPagina As String
    Private _ConBorde As Boolean
    Private _ConTotalDeRegistros As Boolean
#End Region

#Region "DECLARACION VARIABLES GLOBALES"
    Dim G_File As FileStream
    Dim G_WBS As WorkbookSettings
    Dim G_DatosImpersonalizacion As String
    Dim G_ImpersonalizarUser As Boolean
#End Region

#Region "DECLARACION DE CONSTANTES"
    Private Const Separador As String = "^"
#End Region

#Region "ACCESO A LAS PROPIEDADES"
    Public Property RutaArchivo() As String
        Get
            Return Me._RutaArchivo
        End Get
        Set(ByVal value As String)
            Me._RutaArchivo = value.Trim
        End Set
    End Property

    Public Property RegistrosPorHoja() As Integer
        Get
            Return Me._RegistrosXHoja
        End Get
        Set(ByVal value As Integer)
            Me._RegistrosXHoja = value
        End Set
    End Property

    Public Property NombreHoja() As String
        Get
            Return Me._NombreHoja
        End Get
        Set(ByVal value As String)
            Me._NombreHoja = value
        End Set
    End Property

    Public Property FilaInicial() As Integer
        Get
            Return Me._FilaInicial
        End Get
        Set(ByVal value As Integer)
            Me._FilaInicial = value
        End Set
    End Property

    Public Property ColumnaInicial() As Integer
        Get
            Return Me._ColumnaInicial
        End Get
        Set(ByVal value As Integer)
            Me._ColumnaInicial = value
        End Set
    End Property

    Public Property TituloPagina() As String
        Get
            Return Me._TituloPagina
        End Get
        Set(ByVal value As String)
            Me._TituloPagina = value
        End Set
    End Property

    Public Property DataConBorde() As Boolean
        Get
            Return Me._ConBorde
        End Get
        Set(ByVal value As Boolean)
            Me._ConBorde = value
        End Set
    End Property

    Public Property ConTotalDeRegistros() As Boolean
        Get
            Return Me._ConTotalDeRegistros
        End Get
        Set(ByVal value As Boolean)
            Me._ConTotalDeRegistros = value
        End Set
    End Property

    Public ReadOnly Property ErrorProducido() As String
        Get
            Return Me._ErrorMessage
        End Get
    End Property
#End Region

#Region "CONSTRUCTORES"
    Public Sub New()
        G_ImpersonalizarUser = False
        Me._NombreHoja = "Hoja"
        Me._FilaInicial = 1
        Me._ColumnaInicial = 1
        Me._RegistrosXHoja = 60000
        Me.TituloPagina = ""
        Me._ConBorde = False
        Me._ConTotalDeRegistros = False
    End Sub
#End Region

#Region "FUNCIONES Y PROCEDIMIENTOS PUBLICOS"
    Public Sub Dispose()
        Try
            GC.SuppressFinalize(Me)
        Catch ex As Exception
        End Try
    End Sub

    Public Sub ImpersonalizarUsuario(ByVal NombreUsuario As String, ByVal ClaveUsuario As String, ByVal DominioUsuario As String)
        G_DatosImpersonalizacion = NombreUsuario & Separador & ClaveUsuario & Separador & DominioUsuario
        G_ImpersonalizarUser = True
    End Sub

    Public Function GenerarDataTable_A_XLS2(ByVal miDataSet As DataSet, Optional ByVal NombreHojas As String() = Nothing) As Boolean

        Dim _ResultadoBooleano As Boolean = True
        Dim Pagina As Integer = 0, RegInicio As Integer = 1
        Dim NuevaPagina As Boolean = True
        Dim LetraTituloPagina As New WritableCellFormat(New WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD))
        Dim LetraTitulo As New WritableCellFormat(New WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD))
        If Me._ConBorde = True Then LetraTitulo.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK)
        Dim LetraData As New WritableCellFormat(New WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD))
        If Me._ConBorde = True Then LetraData.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK)
        Dim miData As New DataTable
        Dim MaximoHojas As Integer = 0

        For contTablas As Integer = 0 To miDataSet.Tables.Count - 1
            MaximoHojas = MaximoHojas + 1
            If miDataSet.Tables(contTablas).Rows.Count > Me._RegistrosXHoja Then
                MaximoHojas = MaximoHojas + (miDataSet.Tables(contTablas).Rows.Count \ Me.RegistrosPorHoja)
            End If
            'invertidoNAchito = invertidoNAchito - 1
        Next
        'contTablas = invertidoNAchito
        Dim ArrobjS(MaximoHojas) As WritableSheet
        Dim ObjWB As jxl.write.WritableWorkbook
        Dim ProcesarArchivo As Boolean = True
        Dim FilaCampo As String = ""

        Call CrearLibro()
        Dim Fila As String, Posicion As Integer = 0, Valor As String = ""
        ObjWB = jxl.Workbook.createWorkbook(G_File)
        'Dim invertidoNAchito2 As Integer = miDataSet.Tables.Count - 1
        Try
            'Dim nomPag As Integer = invertidoNAchito2 + 2
            For ContTablas As Integer = miDataSet.Tables().Count - 1 To 0 Step -1
                miData = miDataSet.Tables(ContTablas)
                NuevaPagina = True
                For ContRegistros As Integer = 0 To miData.Rows.Count - 1
                    If NuevaPagina = True Then
                        'CREANDO UNA NUEVA HOJA
                        Fila = Me._FilaInicial
                        Pagina = Pagina + 1
                        If IsNothing(NombreHojas) Then
                            ArrobjS(Pagina) = ObjWB.createSheet(Me._NombreHoja & " " & (Pagina), 0)
                        Else
                            ArrobjS(Pagina) = ObjWB.createSheet(NombreHojas(ContTablas), 0)
                        End If
                        'REVISANDO SI SE DEBE AGREGAR TITULO
                        If Me._TituloPagina.Trim <> "" Then
                            ArrobjS(Pagina).addCell(New jxl.write.Label((Posicion + ColumnaInicial), Fila, Me._TituloPagina.Trim, LetraTituloPagina))
                            ArrobjS(Pagina).mergeCells(ColumnaInicial, Fila, ColumnaInicial + miData.Columns.Count, Fila)
                            Fila = Fila + 2
                        End If
                        'CARGANDO TITULOS EN LA HOJA
                        Posicion = 0
                        For posCampo As Integer = 0 To miData.Columns.Count - 1
                            ArrobjS(Pagina).addCell(New jxl.write.Label((Posicion + ColumnaInicial), Fila, miData.Columns(posCampo).Caption, LetraTitulo))
                            Posicion = Posicion + 1
                        Next posCampo
                    End If
                    'AUMENTANDO LA FILA
                    Fila = Fila + 1
                    'CARGANDO LOS CAMPOS
                    Posicion = 0
                    For posCampo As Integer = 0 To miData.Columns.Count - 1
                        FilaCampo = Fila.ToString.Trim & "-" & miData.Columns(posCampo).Caption
                        If IsNothing(miData.Rows(ContRegistros).Item(CInt(posCampo))) Then
                            Valor = ""
                        ElseIf IsDBNull(miData.Rows(ContRegistros).Item(CInt(posCampo))) Then
                            Valor = ""
                        Else
                            Valor = miData.Rows(ContRegistros).Item(CInt(posCampo))
                        End If

                        If IsNumeric(miData.Rows(ContRegistros).Item(posCampo)) Then  'SI ES NUMERICO USA LA POSICION DEL CAMPO
                            ArrobjS(Pagina).addCell(New jxl.write.Label((Posicion + ColumnaInicial), Fila, Valor, LetraData))
                        Else
                            ArrobjS(Pagina).addCell(New jxl.write.Label((Posicion + ColumnaInicial), Fila, Valor, LetraData))
                        End If
                        Posicion = Posicion + 1
                    Next posCampo

                    'REVISAMOS SI TENEMOS QUE CREAR UNA NUEVA PAGINA
                    If RegInicio = Me._RegistrosXHoja Then
                        RegInicio = 1
                        NuevaPagina = True
                    Else
                        RegInicio = RegInicio + 1
                        NuevaPagina = False
                    End If
                Next ContRegistros

                FilaCampo = ""

                If Me._ConTotalDeRegistros = True Then
                    Fila = Fila + 2
                    If Me._TituloPagina.Trim <> "" Then
                        ArrobjS(Pagina).addCell(New jxl.write.Label((ColumnaInicial), Fila, "Total de Registros", LetraTitulo))
                        ArrobjS(Pagina).addCell(New jxl.write.Label((ColumnaInicial + 1), Fila, miData.Rows.Count.ToString.Trim, LetraTitulo))
                    End If
                End If
                MaximoHojas = MaximoHojas - 1
                'invertidoNAchito2 = invertidoNAchito2 - 1
            Next ContTablas
            ObjWB.write()
            ObjWB.close()
        Catch ex As Exception
            _ResultadoBooleano = False
            Me._ErrorMessage = ex.Message.ToString & "[" & FilaCampo & "]"
        Finally
            G_File.Close()
            Call CerrarLibro()
            ArrobjS = Nothing
            ObjWB = Nothing
        End Try
        Return _ResultadoBooleano
    End Function
   
#End Region

#Region "FUNCIONES Y PROCEDIMIENTOS PRIVADOS"
    Private Sub CrearLibro()
        If File.Exists(Me._RutaArchivo) = True Then
            File.Delete(Me._RutaArchivo)
        End If
        G_File = New IO.FileStream(Me._RutaArchivo, IO.FileMode.CreateNew)
        G_WBS = New WorkbookSettings
    End Sub

    Private Sub CerrarLibro()
        G_File.Dispose()
        G_File = Nothing
        G_WBS = Nothing
    End Sub
#End Region

    Private Class TituloExcel
        Private _TituloColuma As String
        Private _Data As String
        Private _ValorCampoFijo As Boolean

        Public Sub New(ByVal TituloColumna As String, ByVal Data As String, ByVal EsFijo As Boolean)
            Me._TituloColuma = TituloColumna
            Me._Data = Data
            Me._ValorCampoFijo = EsFijo
        End Sub

        Public Property TituloColumna() As String
            Get
                Return Me._TituloColuma
            End Get
            Set(ByVal value As String)
                Me._TituloColuma = value
            End Set
        End Property

        Public Property Data() As String
            Get
                Return Me._Data
            End Get
            Set(ByVal value As String)
                Me._Data = value
            End Set
        End Property

        Public Property CampoConValorFijo() As Boolean
            Get
                Return Me._ValorCampoFijo
            End Get
            Set(ByVal value As Boolean)
                Me._ValorCampoFijo = value
            End Set
        End Property
    End Class
End Class
